﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class CompLeyendasFiscDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"
    Public Function existeLeyenda(ByVal pId As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM compleyendasfisc" & _
                                            "WHERE Id = ?Id;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Id", pId)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraLeyenda(ByVal pleyenda As CompLeyendasFiscBE) As Boolean

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO compleyendasfisc(DispFiscal, Norma, Leyenda) " & _
                                            "VALUES (@DispFiscal, @Norma, @Leyenda);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DispFiscal", pleyenda.DispFiscal)
                    myComm.Parameters.AddWithValue("@Norma", pleyenda.Norma)
                    myComm.Parameters.AddWithValue("@Leyenda", pleyenda.Leyenda)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaLeyenda(ByVal pleyenda As CompLeyendasFiscBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE compleyendasfisc " & _
                                            "SET DispFiscal = @DispFiscal, Norma = @Norma, Leyenda = @Leyenda " & _
                                                "WHERE Id = @Id; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DispFiscal", pleyenda.DispFiscal)
                    myComm.Parameters.AddWithValue("@Norma", pleyenda.Norma)
                    myComm.Parameters.AddWithValue("@Leyenda", pleyenda.Leyenda)
                    myComm.Parameters.AddWithValue("@Id", pleyenda.Id)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getLeyenda(ByVal Id As Integer) As CompLeyendasFiscBE
        Dim tmpLeyenda As CompLeyendasFiscBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT Id, DispFiscal, Norma, Leyenda " & _
                                            "FROM compleyendasfisc " & _
                                            "WHERE Id = @Id;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Id", Id)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpLeyenda = CompLeyendasFiscFact.Build(myDR)
                        Else
                            tmpLeyenda = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpLeyenda

        Catch ex As Exception
            Throw
        Finally
            tmpLeyenda = Nothing
        End Try
    End Function

    Public Function getListadoLeyendas() As List(Of CompLeyendasFiscBE)
        Dim LeyendaLs As List(Of CompLeyendasFiscBE) = New List(Of CompLeyendasFiscBE)
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT Id, DispFiscal, Norma, Leyenda " & _
                                           "FROM compleyendasfisc " & _
                                           "ORDER BY Id ASC;"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Leyenda As CompLeyendasFiscBE = CompLeyendasFiscFact.Build(myDR)
                            If (Not Leyenda Is Nothing) Then
                                LeyendaLs.Add(Leyenda)
                            End If
                            Leyenda = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return LeyendaLs
        Catch ex As Exception
            Throw
        Finally
            LeyendaLs = Nothing
        End Try
    End Function
#End Region

End Class
